# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html

from twisted.enterprise import adbapi
from . import settings, items
import pymysql
import logging
import copy

class FirstCrawlSpiderPipeline(object):
    def __init__(self):
        #设置Mysql的参数
        dbparms = dict(
            host=settings.MYSQL_HOST,
            db=settings.MYSQL_DB,
            user=settings.MYSQL_USER,
            passwd=settings.MYSQL_PASSWORD,
            charset=settings.MYSQL_CHARSET,
            cursorclass=pymysql.cursors.DictCursor,
            use_unicode=True
        )
        #开启数据库连接池
        self.db_pool = adbapi.ConnectionPool("pymysql", **dbparms)

    def process_item(self, item, spider):
        #深拷贝，防止出现item被覆盖的情况
        asynItem = copy.deepcopy(item)
        if isinstance(asynItem, items.LineItem) :
            query = self.db_pool.runInteraction(self.do_insert_line, asynItem)
            query.addErrback(self.on_error, spider)
        elif isinstance(asynItem, items.TypeItem) :
            query = self.db_pool.runInteraction(self.do_insert_type, asynItem)
            query.addErrback(self.on_error, spider)
        else :
            logging.log(msg="未知类型" + str(type(asynItem)), level=logging.DEBUG)
        return item

    def do_insert_line(self, cursor, item):
        """
            插入一条车系信息
        """
        select_sql = """SELECT count(l.id) FROM line l WHERE l.id=%s"""
        cursor.execute(select_sql, (item["id"]))
        result = cursor.fetchone()
        if int(result["count(l.id)"]) == 0:
            insert_sql = """INSERT INTO line(`id`, `brand`, `line`, `level`, `mileage`, `power`, `charge`, `ocolor`, `icolor`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
            cursor.execute(insert_sql, (item["id"], item["brand"], item["line"], item["level"], item["mileage"], item["power"], item["charge"], ";".join(item["ocolor"]), ";".join(item["icolor"])))
        else :
            logging.log(msg=item["id"] + "此条信息已经存在", level=logging.DEBUG)

    def do_insert_type(self, cursor, item):
        """
            插入一条车型信息
        """
        select_sql = """SELECT count(t.id) FROM type t WHERE t.id=%s"""
        cursor.execute(select_sql, (item["id"]))
        result = cursor.fetchone()
        if int(result["count(t.id)"]) == 0:
            insert_sql = """INSERT INTO type(`id`, `name`, `price`, `power_type`, `mileage`, `slow_charge`, `quick_charge`, `battery_capacity`, `charge_persent`, `quality_asurance`, `size`, `line_id`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
            params = (item["id"], item["name"], item["price"], item["power_type"], item["mileage"], item["slow_charge"], item["quick_charge"], item["battery_capacity"], item["charge_persent"], item["quality_asurance"], item["size"], item["line_id"])
            cursor.execute(insert_sql, params)
        elif int(result["count(t.id)"]) == 1:
            update_sql = """UPDATE type SET `name`=%s, `price`=%s, `power_type`=%s, `mileage`=%s, `slow_charge`=%s, `quick_charge`=%s, `battery_capacity`=%s, `charge_persent`=%s, `quality_asurance`=%s, `size`=%s, `line_id`=%s WHERE `id`=%s"""
            params = (item["name"], item["price"], item["power_type"], item["mileage"], item["slow_charge"], item["quick_charge"], item["battery_capacity"], item["charge_persent"], item["quality_asurance"], item["size"], item["line_id"], item["id"])
            cursor.execute(update_sql, params)
        else :
            logging.log(msg="数据异常" + str(item), level=logging.DEBUG)


    def on_error(self, failure, spider):
        """
            打印记录错误信息
        """
        logging.log(msg=failure, level=logging.ERROR)


    def close_spider(self, spider) :
        self.db_pool.close()